package com.lry.basic.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.Data;
import org.apache.commons.lang.time.DateUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

public class Test2 {
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty( value = "姓名",index = 0)
        private String name;
        @ExcelProperty( value = "年龄",index = 1)
        private int age;
        @ExcelProperty( value = "学校",index = 2)
        private String school;
    }



    @org.junit.jupiter.api.Test
    public void t() throws IOException {
        File file = new File("D:\\1.txt");
        System.out.println(file.exists());
        BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file)));
        writer.write("hello");
        writer.flush();
        writer.close();
        System.out.println(file.exists());
    }

     /**
     * 多个sheet导入测试
     * @throws FileNotFoundException
     */
    @org.junit.jupiter.api.Test
    public void sheetImport() throws IOException {
        File file = new File("D:/1.xlsx");
        System.out.println(file.exists());
        OutputStream outputStream = new FileOutputStream(file);
        System.out.println(file.exists());

        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1").head(TestVO.class).build();
        WriteSheet sheet2 = EasyExcel.writerSheet(1, "sheet2").head(TestVO.class).build();

        excelWriter.write(data(),sheet1);
        excelWriter.write(data(),sheet2);

        excelWriter.finish();
    }

    private  List<TestVO> data(){
        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            TestVO testVO = new TestVO();
            testVO.setAge(i + 20);
            testVO.setName("vdsosd" + i);
            testVO.setSchool("schosdsdol" + i);
            dataList.add(testVO);
        }
        return dataList;
    }


    @org.junit.jupiter.api.Test
    public void read() throws IOException {

        MyDataListener myDataListener = new MyDataListener();
        ExcelReader excelReader = EasyExcel.read("C:\\\\Users\\\\liurenyou\\\\Desktop\\\\新建 XLS 工作表.xls",Feedback.class,myDataListener).build();
        ReadSheet sheet1 = EasyExcel.readSheet(0, "Sheet1").head(Feedback.class).build();
        excelReader.read(sheet1);
        // 数据由监听器保存，通过getDatas()获取每行数据
        List<Feedback> feedbacks = myDataListener.getDatas();


        MyDataListener myDataListener2 = new MyDataListener();
        ExcelReader excelReader2 = EasyExcel.read("C:\\\\Users\\\\liurenyou\\\\Desktop\\\\新建 XLS 工作表.xls",Feedback.class,myDataListener2).build();
        ReadSheet sheet2 = EasyExcel.readSheet(1, "Sheet2").head(Feedback.class).build();
        excelReader2.read(sheet2);
        // 数据由监听器保存，通过getDatas()获取每行数据
        Map<String,Feedback> feedback2Map = myDataListener2.getDatas().stream()
                .collect(Collectors.toMap(Feedback::getId, Function.identity(), (k1, k2) -> k2));


        for (Feedback feedback : feedbacks) {
            Date startTime = feedback2Map.get(feedback.getId()).getTime();

            System.out.println("update t_feedback set tech_deal_second="+(feedback.getTime().getTime() - startTime.getTime())/1000+" where id ="+feedback.getId().replace("a","")+";");

        }


        excelReader.finish();
    }

    @Data
    @ColumnWidth(20)
    public static class Feedback {

        @ExcelProperty( value = "姓名",index = 0)
        private String id;

        @ExcelProperty( value = "学校",index = 1)
        private Date time;


    }

    public class MyDataListener extends AnalysisEventListener<Feedback> {

        private List<Test2.Feedback> datas = new ArrayList<>();

        @Override
        public void invoke(Feedback object, AnalysisContext context) {
            datas.add(object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            System.out.println("所有数据解析完成！");
        }

        public List<Feedback> getDatas() {
            return datas;
        }
    }
}
